
package model.dao;
import model.entities.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.StudentDAOService;


public class StudentDAO implements StudentDAOService {
    private static StudentDAO studentDAO;
    
    public static StudentDAO getInstance(){
        if(studentDAO == null){
        studentDAO = new StudentDAO();
        }
        return studentDAO;
    
    }
    
    @Override
    public List<Student> getStudentsList(){
       ArrayList<Student> listStudent = new ArrayList<Student>(); 
       Connection con = ConnectionFactory.getConnection();
       String sql = "SELECT * FROM tblStudent WHERE IsActived='true'";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student std = new Student();
                std.setStudentID(rs.getInt("StudentID"));
                std.setName(rs.getString("Name"));
                std.setGender(rs.getBoolean("Gender"));
                std.setBirthday(rs.getDate("Birthday"));
                std.setPhone(rs.getString("Phone"));
                std.setEmail(rs.getString("Email"));
                std.setAddress(rs.getString("Address"));
                std.setNotes(rs.getString("Notes"));
                listStudent.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listStudent;
    }
    
    @Override
    public Student getStudentByID(int studentID){
        Student std = new Student();
        Connection con = ConnectionFactory.getConnection();
        String sql = "SELECT * FROM tblStudent WHERE StudentID=? AND IsActived='true'";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, studentID);
            ResultSet rs = pstmt.executeQuery();
            std.setStudentID(rs.getInt("StudentID"));
            std.setName(rs.getString("Name"));
            std.setGender(rs.getBoolean("Gender"));
            std.setBirthday(rs.getDate("Birthday"));
            std.setPhone(rs.getString("Phone"));
            std.setEmail(rs.getString("Email"));
            std.setAddress(rs.getString("Address"));
            std.setNotes(rs.getString("Notes"));
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return std;
    }    

    @Override
    public boolean insertStudent(Student S) {
        String query = "INSERT INTO tblStudent VALUES(?,?,?,?,?,?,?,?)";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, S.getName());
            pstmt.setBoolean(2, S.isGender());
            pstmt.setDate(3, (Date) S.getBirthday());
            pstmt.setString(4, S.getPhone());
            pstmt.setString(5, S.getEmail());
            pstmt.setString(6, S.getAddress());
            pstmt.setString(7, S.getNotes());
            pstmt.setBoolean(8, S.isIsActived());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean updateStudent(Student S) {
        String query = "UPDATE tblStudent SET Name=?,Gender=?,Birthday=?,Phone=?,Email=?,[Address]=?,Notes=?,IsActived=?" +
                        "WHERE StudentID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, S.getName());
            pstmt.setBoolean(2, S.isGender());
            pstmt.setDate(3, (Date) S.getBirthday());
            pstmt.setString(4, S.getPhone());
            pstmt.setString(5, S.getEmail());
            pstmt.setString(6, S.getAddress());
            pstmt.setString(7, S.getNotes());
            pstmt.setBoolean(8, S.isIsActived());
            pstmt.setInt(9, S.getStudentID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean deleteStudent(int id) {
        String query = "DELETE tblStudent WHERE StudentID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } 
    }

    @Override
    public List<Student> findByID(int id) {
        List<Student> lstStudent = new ArrayList<Student>();
        String query = "SELECT * FROM tblStudent WHERE StudentID=? AND IsActived='true'";
        Connection conn = ConnectionFactory.getConnection();
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student s = new Student();
                s.setStudentID(rs.getInt("StudentID"));
                s.setName(rs.getNString("Name"));
                s.setAddress(rs.getNString("Address"));
                s.setBirthday(rs.getDate("Birthday"));
                s.setEmail(rs.getString("Email"));
                s.setGender(rs.getBoolean("Gender"));
                s.setIsActived(rs.getBoolean("IsActived"));
                s.setNotes(rs.getNString("Notes"));
                s.setPhone(rs.getString("Phone"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstStudent;
    }

    @Override
    public List<Student> findByName(String name) {
        List<Student> lstStudent = new ArrayList<Student>();
        String query = "SELECT * FROM tblStudent WHERE StudentID=? AND IsActived='true'";
        Connection conn = ConnectionFactory.getConnection();
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setNString(1, name);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Student s = new Student();
                s.setStudentID(rs.getInt("StudentID"));
                s.setName(rs.getNString("Name"));
                s.setAddress(rs.getNString("Address"));
                s.setBirthday(rs.getDate("Birthday"));
                s.setEmail(rs.getString("Email"));
                s.setGender(rs.getBoolean("Gender"));
                s.setIsActived(rs.getBoolean("IsActived"));
                s.setNotes(rs.getNString("Notes"));
                s.setPhone(rs.getString("Phone"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstStudent;
    }
}
